Imports System
Imports System.Windows.Forms
Imports System.Reflection
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
''' -----------------------------------------------------------------------------
''' Project	 : ConnectionServer
''' Class	 : Connection
''' 
''' -----------------------------------------------------------------------------
''' <summary>
''' The ConnectionServer Class allows easy access to all possible datasources
''' It will detect what machine it is on and based on that information switch 
''' between the Development SQL Server/Databases or Production.
''' It will return connection to either SQL Databases or OLE databases (i.e. Access)
''' </summary>
''' <remarks>
''' </remarks>
''' <history>
''' </history>
''' -----------------------------------------------------------------------------
<Serializable()> Public Class Connection
    Inherits MarshalByRefObject
    Implements IDisposable
    Public Enum SourceDataBase
        CMT
    End Enum
    Private Const GLOBAL_SETTINGS As String = ""


 
    Private Const LOCAL As String = "LocalHost"

    Private moConn As SqlClient.SqlConnection
    Public Sub New()


        Dim loAppSettings As AppSettingsReader = New AppSettingsReader

      

    End Sub
    Public Sub New(ByVal peDataBase As SourceDataBase)
        Me.New()
        GetConnection(peDataBase)
    End Sub


 
    Public ReadOnly Property Connection() As SqlClient.SqlConnection
        Get
            Return moConn
        End Get
    End Property
    Public Function GetDataSet(ByVal psSQL As String) As DataSet
        Using loComm As SqlCommand = Command(psSQL)
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataSet = New DataSet
            loAdp.Fill(loDT)
            loComm.Connection.Close()

            Return loDT
        End Using
    End Function

    Public Function GetDataTable(ByVal psSQL As String) As DataTable
        Using loComm As SqlCommand = Command(psSQL)
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()

            Return loDT
        End Using
    End Function
    Public Function GetDataReader(ByVal psSQL As String) As SqlDataReader
        Using loComm As SqlCommand = Command(psSQL)
            Return loComm.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        End Using
    End Function


    Public ReadOnly Property Command(ByVal psSQL As String) As SqlClient.SqlCommand
        Get
            Return New SqlClient.SqlCommand(TestForSQLInjection(psSQL), moConn)
        End Get
    End Property
    Public Function ExecuteScalar(ByVal psSQL As String) As Object

        Using loComm As SqlCommand = Command(psSQL)

            loComm.Transaction = loComm.Connection.BeginTransaction
            Dim loValue As Object = loComm.ExecuteScalar
            If Not Environment.GetEnvironmentVariable("TestMode") Is Nothing AndAlso _
                CBool(Environment.GetEnvironmentVariable("TestMode")) Then
                loComm.Transaction.Rollback()
            Else
                loComm.Transaction.Commit()
            End If
            loComm.Connection.Close()
            Return loValue
        End Using
    End Function
    Public Function TestForSQLInjection(ByVal psSQL As String) As String
        Dim rexSql As Regex = New Regex("/exec(\s|\+)+(s|x)p\w+/ix")
        Dim rexSql2 As Regex = New Regex("(\-\-)")

        If Not rexSql.Match(psSQL).Success And Not rexSql2.Match(psSQL).Success Then
            Return psSQL
        End If
        Throw New Exception("SQL Injection attack possible with:" & psSQL)
    End Function

    Public Function GetConnection(ByVal peSourceDataBase As SourceDataBase) As System.Data.SqlClient.SqlConnection


        moConn = New SqlClient.SqlConnection(GetConnectionString(peSourceDataBase))
        moConn.Open()
        Return moConn

    End Function
    Public ReadOnly Property WebServerName() As String
        Get
            Dim loAppSettings As AppSettingsReader = New AppSettingsReader

            Return loAppSettings.GetValue("WebServerName", GetType(System.String)).ToString
        End Get
    End Property
    Public Function GetConnectionString(ByVal peSourceDataBase As SourceDataBase) As String
        Return ConfigurationManager.ConnectionStrings(GetDataBaseName(peSourceDataBase)).ConnectionString
    End Function
    Public Function GetDataBaseName(ByVal peSourceDataBase As SourceDataBase) As String
        Select Case peSourceDataBase
            Case SourceDataBase.CMT
                Return "CMT"
            Case Else
                Throw New SystemException("Database Name not found")
        End Select
    End Function

    Protected Overrides Sub Finalize()

        If Not moConn Is Nothing Then

            moConn = Nothing

        End If

        MyBase.Finalize()
    End Sub

    Public Sub Dispose() Implements System.IDisposable.Dispose
        If Not moConn Is Nothing Then
            Try
                While moConn.State = ConnectionState.Open
                    moConn.Close()
                End While
            Finally
                moConn = Nothing
            End Try
        End If
    End Sub
End Class
